@echo off
mkdir logs
set logfile=logs\allvalidate_%db2_server_name%.txt

rem Date /T >>%logfile%
rem TIME /T >>%logfile%
rem %~dp0 is expanded pathname of the current script under NT

echo ----- The following SP need rebind----->>%logfile%
db2 -x "SELECT  ' values('||CHR(39)||'Rebinding:'|| RTRIM(r.ROUTINENAME)||CHR(39)||')@' ||varchar(CHR(13) || CHR(10)) ||' rebind package '|| RTRIM(r.routineschema)|| '.P'|| SUBSTR(CHAR(r.lib_id+10000000),2)|| ' @ ' FROM SYSCAT.routines r WHERE r.routinetype = 'P' AND ((r.origin = 'Q' AND r.valid != 'Y') OR EXISTS ( SELECT 1 FROM syscat.packages WHERE pkgschema = r.routineschema AND pkgname = 'P'||SUBSTR(CHAR(r.lib_id+10000000),2) AND valid !='Y' ))"  >> %logfile%

echo -----Below are invalid views----->>%logfile%
db2 -x "SELECT  '--' || RTRIM(viewschema) || '.' || RTRIM(viewname) AS viewname FROM SYSCAT.views WHERE valid = 'X' ORDER BY viewname" >> %logfile%

db2 -td@ -x -f %logfile%
start  %logfile%
